/**
 * Oracle数据库触发器脚本
 *
 * @author chenw
 * @create 2017-06-09 22:46
 * @email javacspring@gmail.com
 */
-- 创建SEQ序列
CREATE SEQUENCE SEQ_OS_DEX_SYNC INCREMENT BY 1 START WITH 1 MAXVALUE 999999999999 CACHE 20;
-- 创建数据同步表OS_DEX_SYNC   OS_DEX_MEDIUM_SYNC   OS_DEX_LOW_SYNC
CREATE TABLE OS_DEX_SYNC
(
  TAB  VARCHAR(50),
  ACT  INT       DEFAULT 1,
  CTM  TIMESTAMP DEFAULT SYSTIMESTAMP,
  DATA CLOB,
  ATT  SMALLINT  DEFAULT 0
);
COMMENT ON COLUMN OSC.OS_DEX_SYNC.TAB IS '同步表名';
COMMENT ON COLUMN OSC.OS_DEX_SYNC.ACT IS '同步行为 1:Create 2:Update 4:Delete';
COMMENT ON COLUMN OSC.OS_DEX_SYNC.CTM IS '同步数据创建时间';
COMMENT ON COLUMN OSC.OS_DEX_SYNC.DATA IS '同步数据内容';
COMMENT ON COLUMN OSC.OS_DEX_SYNC.ATT IS '是否为附件表数据 0:否 1:是';
COMMENT ON TABLE OSC.OS_DEX_SYNC IS '数据交换表';

-- 创建测试表A
CREATE TABLE TEST_SYNC_A
(
  ID   VARCHAR2(36),
  NAME VARCHAR2(200),
  DATA VARCHAR2(300),
  CONTENT CLOB
);
-- 创建测试表B
CREATE TABLE TEST_SYNC_B
(
  ID   VARCHAR2(36),
  NAME VARCHAR2(200),
  DATA VARCHAR2(300),
  CON  NCLOB,
  COL1  CHAR(1),
  COL2  NCHAR(1),
  COL3  VARCHAR2(255),
  COL4  NVARCHAR2(255),
  COL5  CLOB,
  COL6  NUMBER,
  COL7  NUMBER(20,0),
  COL8  DATE,
  COL9  TIMESTAMP,
  COL11  FLOAT(6),
  COL10  NUMBER
);

-- 创建触发器
CREATE OR REPLACE TRIGGER OS_TRG_C_TEST_SYNC_A
AFTER INSERT
  ON TEST_SYNC_A
FOR EACH ROW
  BEGIN
    INSERT INTO OS_DEX_SYNC (SYN_ID, TAB, ACT, CTM, DATA, ATT)
    VALUES (SEQ_OS_DEX_SYNC.nextval, 'TEST_SYNC_A', 1, SYSTIMESTAMP, '{"ID":"' || :NEW.ID || '","NAME":"' || :NEW.NAME || '","DATA":"' || :NEW.DATA || '","CONTENT":"' || :NEW.CONTENT || '"}','1');
  END;

CREATE OR REPLACE TRIGGER OS_TRG_U_TEST_SYNC_A
AFTER UPDATE
  ON TEST_SYNC_A
FOR EACH ROW
  BEGIN
    INSERT INTO OS_DEX_SYNC (SYN_ID, TAB, ACT, CTM, DATA,ATT)
    VALUES (SEQ_OS_DEX_SYNC.nextval, 'TEST_SYNC_A', 2, SYSTIMESTAMP, '{"ID":"' || :NEW.ID || '","NAME":"' || :NEW.NAME || '","DATA":"' || :NEW.DATA || '","CONTENT":"' || :NEW.CONTENT || '"}','1');
  END;

CREATE OR REPLACE TRIGGER OS_TRG_D_TEST_SYNC_A
BEFORE DELETE
  ON TEST_SYNC_A
FOR EACH ROW
  BEGIN
    INSERT INTO OS_DEX_SYNC (SYN_ID, TAB, ACT, CTM, DATA, ATT) VALUES (SEQ_OS_DEX_SYNC.nextval, 'TEST_SYNC_A', 4, SYSTIMESTAMP, '{"ID":"' || :OLD.ID || '","NAME":"' || :OLD.NAME || '","DATA":"' || :OLD.DATA || '","CONTENT":"' || :OLD.CONTENT || '"}', '1');
  END;
-- 测试表B触发器
CREATE OR REPLACE TRIGGER OS_TRG_C_TEST_SYNC_B
AFTER INSERT
  ON TEST_SYNC_B
FOR EACH ROW
  BEGIN
    INSERT INTO OS_DEX_SYNC (SYN_ID, TAB, ACT, CTM, data, ATT)
    VALUES (SEQ_OS_DEX_SYNC.nextval, 'TEST_SYNC_B', 1, SYSTIMESTAMP, '{"ID":"' || :NEW.ID ||
                                                                     '","NAME":"' || :NEW.NAME ||
                                                                     '","DATA":"' || :NEW.DATA ||
                                                                     '","CON":"' || :NEW.CON ||
                                                                     '","COL1":"' || :NEW.COL1 ||
                                                                     '","COL2":"' || :NEW.COL2 ||
                                                                     '","COL3":"' || :NEW.COL3 ||
                                                                     '","COL4":"' || :NEW.COL4 ||
                                                                     '","COL5":"' || :NEW.COL5 ||
                                                                     '","COL6":"' || :NEW.COL6 ||
                                                                     '","COL7":"' || :NEW.COL7 ||
                                                                     '","COL8":"' || TO_CHAR(:NEW.COL8,'YYYY-MM-DD HH24:MI:SS') ||
                                                                     '","COL9":"' || TO_CHAR(:NEW.COL9,'YYYY-MM-DD HH24:MI:SS.FF9') ||
                                                                     '","COL10":"' || :NEW.COL10 ||
                                                                     '","COL11":"' || :NEW.COL11 ||
                                                                     '"}','0');
  END;

CREATE OR REPLACE TRIGGER OS_TRG_U_TEST_SYNC_B
AFTER UPDATE
  ON TEST_SYNC_B
FOR EACH ROW
  BEGIN
    INSERT INTO OS_DEX_SYNC (SYN_ID, TAB, ACT, CTM, DATA, ATT)
    VALUES (SEQ_OS_DEX_SYNC.nextval, 'TEST_SYNC_B', 2, SYSTIMESTAMP, '{"ID":"' || :NEW.ID ||
                                                                     '","NAME":"' || :NEW.NAME ||
                                                                     '","DATA":"' || :NEW.DATA ||
                                                                     '","CON":"' || :NEW.CON ||
                                                                     '","COL1":"' || :NEW.COL1 ||
                                                                     '","COL2":"' || :NEW.COL2 ||
                                                                     '","COL3":"' || :NEW.COL3 ||
                                                                     '","COL4":"' || :NEW.COL4 ||
                                                                     '","COL5":"' || :NEW.COL5 ||
                                                                     '","COL6":"' || :NEW.COL6 ||
                                                                     '","COL7":"' || :NEW.COL7 ||
                                                                     '","COL8":"' || TO_CHAR(:NEW.COL8,'YYYY-MM-DD HH24:MI:SS') ||
                                                                     '","COL9":"' || TO_CHAR(:NEW.COL9,'YYYY-MM-DD HH24:MI:SS.FF9') ||
                                                                     '","COL10":"' || :NEW.COL10 ||
                                                                     '","COL11":"' || :NEW.COL11 ||
                                                                     '"}','0');
  END;

CREATE OR REPLACE TRIGGER "OSC"."OS_TRG_D_TEST_SYNC_B"
BEFORE DELETE
  ON TEST_SYNC_B
FOR EACH ROW
  BEGIN
    INSERT INTO OS_DEX_SYNC (SYN_ID, TAB, ACT, CTM, DATA, ATT) VALUES (SEQ_OS_DEX_SYNC.nextval, 'TEST_SYNC_B', 4, SYSTIMESTAMP, '{"ID":"' || :OLD.ID ||
                                                                     '","NAME":"' || :OLD.NAME ||
                                                                     '","DATA":"' || :OLD.DATA ||
                                                                     '","CON":"' || :OLD.CON ||
                                                                     '","COL1":"' || :OLD.COL1 ||
                                                                     '","COL2":"' || :OLD.COL2 ||
                                                                     '","COL3":"' || :OLD.COL3 ||
                                                                     '","COL4":"' || :OLD.COL4 ||
                                                                     '","COL5":"' || :OLD.COL5 ||
                                                                     '","COL6":"' || :OLD.COL6 ||
                                                                     '","COL7":"' || :OLD.COL7 ||
                                                                     '","COL8":"' || TO_CHAR(:OLD.COL8,'YYYY-MM-DD HH24:MI:SS') ||
                                                                     '","COL9":"' || TO_CHAR(:OLD.COL9,'YYYY-MM-DD HH24:MI:SS.FF9') ||
                                                                     '","COL10":"' || :OLD.COL10 ||
                                                                     '","COL11":"' || :OLD.COL11 ||
                                                                     '"}','0');
  END;


